//   -------------------- iSharePoint -------------------- //
//  
//   Company:	IT Complex, LLC
//   Project:	Smart.Common
//   File name:	SqlSession.cs
//   Developer:	Igor Solomatov
//   Created:	28.08.2012
//  
//   -------------------- iSharePoint -------------------- //

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Smart.SQL
{
	/// <summary>
	/// SQL session (command + connection)
	/// </summary>
	[Localizable(false)]
	public class SqlSession : SqlSessionBase
	{
		public SqlSession(SqlSettings settings)
			: base(settings)
		{
		}

		public void Select(Func<SqlDataReader, bool> handler, string tableName, params string[] columns)
		{
			ExecuteReader(
				handler,
				CommandBehavior.SingleResult,
				CommandType.Text,
				string.Format(Templates.GenericSelect, tableName, string.Join(",", columns)));
		}

		public void Select(Func<SqlDataReader, bool> handler, string tableName, string col1, object val1, params string[] columns)
		{
			var parameters = new List<SqlParameter>();

			ExecuteReader(
				handler,
				CommandBehavior.SingleResult,
				CommandType.Text,
				string.Format(Templates.GenericSelectWhere1, tableName, string.Join(",", columns), col1, parameters.AddParameter("v", val1)),
				parameters.ToArray());
		}

		public void Select(Func<SqlDataReader, bool> handler, string tableName, string col1, object val1, string col2, object val2, params string[] columns)
		{
			var parameters = new List<SqlParameter>();

			ExecuteReader(
				handler,
				CommandBehavior.SingleResult,
				CommandType.Text,
				string.Format(Templates.GenericSelectWhere2, tableName, string.Join(",", columns), col1, parameters.AddParameter("v", val1), col2, parameters.AddParameter("v", val2)),
				parameters.ToArray());
		}

		public Dictionary<string, object> Select(string tableName, string col1, object val1, params string[] columns)
		{
			var parameters = new List<SqlParameter>();

			return
				ExecuteReaderRow(
					CommandType.Text,
					String.Format(Templates.GenericSelectWhere1, tableName, string.Join(",", columns), col1, parameters.AddParameter("v", val1)),
					columns,
					parameters.ToArray());
		}

		public Dictionary<string, object> Select(string tableName, string col1, object val1, string col2, object val2, params string[] columns)
		{
			var parameters = new List<SqlParameter>();

			return
				ExecuteReaderRow(
					CommandType.Text,
					String.Format(Templates.GenericSelectWhere2, tableName, string.Join(",", columns), col1, parameters.AddParameter("v", val1), col2, parameters.AddParameter("v", val2)),
					columns,
					parameters.ToArray());
		}

		public object Lookup(string tableName, string columnName, string col1, object val1)
		{
			var parameters = new List<SqlParameter>();

			return
				ExecuteScalar(
					CommandType.Text,
					String.Format(Templates.GenericSelectWhere1, tableName, columnName, col1, parameters.AddParameter("v", val1)),
					parameters.ToArray());
		}

		public object Lookup(string tableName, string columnName, string col1, object val1, string col2, object val2)
		{
			var parameters = new List<SqlParameter>();

			return
				ExecuteScalar(
					CommandType.Text,
					String.Format(Templates.GenericSelectWhere2, tableName, columnName, col1, parameters.AddParameter("v", val1), col2, parameters.AddParameter("v", val2)),
					parameters.ToArray());
		}

		public object Insert(string tableName, string primaryKey, Dictionary<string, object> values)
		{
			string cols;
			string vals;

			var parameters = new List<SqlParameter>();

			values.GetInsertParameters("v", parameters, out cols, out vals);

			return
				ExecuteScalar(
					CommandType.Text,
					String.Format(Templates.GenericInsert, tableName, cols, vals, primaryKey),
					parameters.ToArray()).PatchFromSql();
		}

		public bool Update(string tableName, string primaryKey, object primaryValue, Dictionary<string, object> values)
		{
			var parameters = new List<SqlParameter>();

			string cv;

			values.GetUpdateParameters("v", parameters, out cv);

			return
				ExecuteNonQuery(
					CommandType.Text,
					String.Format(Templates.GenericUpdate, tableName, cv, primaryKey, parameters.AddParameter("id", primaryValue)),
					parameters.ToArray()) == 1;
		}


		public bool Delete(string tableName, string primaryKey, object primaryValue)
		{
			var parameters = new List<SqlParameter>();

			return
				ExecuteNonQuery(
					CommandType.Text,
					string.Format(Templates.GenericDelete, tableName, primaryKey, parameters.AddParameter("id", primaryValue)),
					parameters.ToArray()) == 1;
		}

		public bool Delete(string tableName, string col1, object val1, string col2, object val2)
		{
			var parameters = new List<SqlParameter>();

			return
				ExecuteNonQuery(
					CommandType.Text,
					string.Format(Templates.GenericDelete2, tableName, col1, parameters.AddParameter("v", val1), col2, parameters.AddParameter("v", val2)),
					parameters.ToArray()) > 0;
		}

		public bool ExecuteDDL(string text, params SqlParameter[] parameters)
		{
			try
			{
				ExecuteNonQuery(CommandType.Text, text, parameters);

				return true;
			}
			catch (Exception x)
			{
				H.LX("SqlSession.ExecuteDDL", "ExecuteDDL text: " + text, x);

				return false;
			}
		}

		public bool ExecuteMultipleDDL(params string[] cmds)
		{
			if (cmds == null) throw new ArgumentNullException("cmds");

			return
				cmds.All(cmd => ExecuteDDL(cmd));
		}

		/// <summary>
		/// Only rename table in data base
		/// </summary>
		public bool RenameTable(String oldName, String newName)
		{
			var oldNameSqlParam = new SqlParameter("oldName", oldName);
			var newNameSqlParam = new SqlParameter("newName", newName);

			return
				ExecuteDDL(Templates.GenericRenameTable, oldNameSqlParam, newNameSqlParam);
		}

		public bool RenameColumn(string tableName, string oldName, string newName)
		{
			var sqlCmd = String.Format(Templates.GenericRenameColumn, tableName, oldName, newName);
			return
				ExecuteDDL(sqlCmd);
		}

		public bool AddColumn(string tableName, string columnDefinition)
		{
			var sqlCmd = String.Format(Templates.GenericAddColumn, tableName, columnDefinition);
			return
				ExecuteDDL(sqlCmd);
		}

		public bool DropColumn(string tableName, string columnName)
		{
			var sqlCmd = String.Format(Templates.GenericDropColumn, tableName, columnName);
			return
				ExecuteDDL(sqlCmd);
		}

		#region Nested type: Templates

		/// <summary>
		/// Templates values (never changed)
		/// </summary>
		public static class Templates
		{
			public const string GenericInsert = @"INSERT INTO [dbo].[{0}] ({1}) OUTPUT INSERTED.[{3}] VALUES ({2});";
			public const string GenericUpdate = @"UPDATE [dbo].[{0}] SET {1} WHERE ([{2}] = {3});";
			public const string GenericDelete = @"DELETE FROM [dbo].[{0}] WHERE ([{1}] = {2});";
			public const string GenericDelete2 = @"DELETE FROM [dbo].[{0}] WHERE ([{1}] = {2}) AND ([{3}] = {4});";
			public const string GenericSelect = @"SELECT {1} FROM [dbo].[{0}]";
			public const string GenericSelectWhere1 = @"SELECT {1} FROM [dbo].[{0}] WHERE ([{2}] = {3})";
			public const string GenericSelectWhere2 = @"SELECT {1} FROM [dbo].[{0}] WHERE ([{2}] = {3}) AND ([{4}] = {5})";
			public const string GenericCreateTable = @"CREATE TABLE [dbo].[{0}] ({1});";
			public const string GenericDropTable = @"DROP TABLE [dbo].[{0}];";
			public const string GenericRenameTable = @"EXEC sp_rename @oldName, @newName;";
			public const string GenericRenameColumn = @"SP_RENAME '{0}.[{1}]' , '{2}', 'COLUMN';";
			public const string GenericAddColumn = @"ALTER TABLE [dbo].[{0}] ADD {1};";
			public const string GenericDropColumn = @"ALTER TABLE [dbo].[{0}] DROP COLUMN [{1}];";

			static Templates()
			{
			}
		}

		#endregion
	}
}